use "$datapath/Prediction_DiscountedReturns.dta", clear // equity return

* merge with earning-price and dividend-price
merge 1:1 country datem using "$datapath/MSCI_exceldata_to_DTA.dta", gen(merge_msci)  

* encode string variable "country" in labeled numerical value "id"
sencode country, gen(id)
xtset id datem // set panel id

***************************************************************
* US variables
preserve
	qui keep if country == "unitedstates"
	foreach v of varlist dy r r1 ep {
		qui rename `v' `v'_us
	}
	 qui keep *_us datem 
	 
	 tempfile theus
	 qui save `theus', replace
restore
merge m:1 datem using `theus'
drop _merge
* drop the U.S.
drop if country == "unitedstates"
sort country datem
***************************************************************

******************************************************************************
*** SAMPLE
bys datem : egen Ncountrytemp = count(id) if !mi(r1) & !mi(dy) & !mi(ep) & !mi(r) // by month, count non missing observations across countries
bys datem : egen Ncountry = mean(Ncountrytemp)
drop if mi(Ncountry) // drop if a month has no observations 

*** summary statistics
replace r1 = r1*1200 // annual return in percent
replace r = r*1200 // annual return in percent

replace r1_us = r1_us*1200 // annual return in percent

ta country, sum(r) // divide standard deviation by sqrt(12)

*******************************************************************************
*** Variables Block
*******************************************************************************

* differential with U.S. 
gen dr1 = r1 - r1_us
keep datem country dy dr1 ep r

global Qvar = "dy ep r" 
gen month = mofd(dofm(datem))

** Quintiles:
foreach q of global Qvar {
	display as text "`q'"
	// quintiles separately in each year
	qui generate Q`q' = .
	qui levelsof month, local(dm)
	foreach d of local dm {
		qui xtile temp = `q' if month==`d', nq(5)
		qui replace Q`q' = temp if month==`d'
		qui drop temp
	}
}
sort  datem Qdy dy

*** average returns by quintiles
foreach q of global Qvar {
	bys datem Q`q' : egen P_`q' = mean(dr1)
}
sort  datem Qr r

* table
foreach q of global Qvar {
	matrix m = J(5,6,.)
	preserve
		collapse P_`q', by(datem Q`q')
		drop if mi(Q`q')
		reshape wide P_`q' , i(datem) j(Q`q')
		gen HML_`q'= P_`q'5 - P_`q'1
		
		local k = 1
		foreach v of varlist P* HML* {
			qui sum `v', d
			matrix m[1,`k'] = round(r(mean),.01)
			matrix m[2,`k'] = round(r(p50),.01)
			matrix m[3,`k'] = round(r(sd)/sqrt(12),.01)
			matrix m[4,`k'] = round(r(skewness),.01)
			matrix m[5,`k'] = round(r(kurtosis),.01)
			
			local k = `k' + 1
		}
		matrix list m
		
		matrix colnames m = "Q1" "Q2" "Q3" "Q4" "Q5" "HML"
		matrix rownames m = "Mean" "Median" "Standard_Deviation" "Skew" "Kurtosis"
		
		outtable using "$tabpath/tableC1`q'", ///
		mat(m) replace longtable nobox  center  ///
		caption("Descriptive Statistics of Portfolio Return: Predictor `q'. 73 countries. Sample 1970m1:2019m2") clabel("tab:tableC1`q'")

	restore
}






* end
******************************************************************************
